--EXEC sp_parent_products_list_by_type

 Create   PROC sp_parent_products_list_by_type
 AS
 BEGIN
 	DECLARE @results TABLE( ProcessStep_ID INT, IST_Manufacturing_Part_Number VARCHAR (50), P_Type VARCHAR (50) )
 	DECLARE @proc_id VARCHAR (20)
 	DECLARE @mft_part_no VARCHAR (50)
 	DECLARE @ps_type VARCHAR (50)
 	DECLARE ncursor CURSOR FOR
 	
 	SELECT PROCESS_STEP.PROCESSSTEP_ID, PRODUCT.PART_NUMBER, P_TYPE FROM PROCESS_STEP, PRODUCT WHERE PROCESS_STEP.PROCESSSTEP_ID = PRODUCT.PROCESSSTEP_ID AND PRODUCT.P_TYPE is null AND PRODUCT.Is_Approved=0 AND PS_TYPE_NAME = 'Finished Goods' ORDER BY PRODUCT.PART_NUMBER
 	OPEN ncursor
 	FETCH NEXT FROM ncursor INTO @proc_id, @mft_part_no, @ps_type
 	WHILE @@fetch_status = 0
 		BEGIN
 			INSERT INTO @results VALUES(@proc_id, @mft_part_no, @ps_type )
 			SET @proc_id = null
 			SET @mft_part_no = null
 			SET @ps_type = null
 			
 			FETCH NEXT FROM ncursor INTO @proc_id,@mft_part_no,@ps_type 
 		END
 	CLOSE ncursor
 	DEALLOCATE ncursor
  
 	INSERT INTO @results VALUES(null,'---- BOARD -----','---- BOARD -----')
  
 	DECLARE bcursor CURSOR FOR
 	SELECT PROCESS_STEP.ProcessStep_ID, Part_Number, P_Type FROM PRODUCT,PROCESS_STEP WHERE PRODUCT.P_Type = 'BOARD' and PRODUCT.P_Parent_Product_Id = -1 and PRODUCT.Is_Approved = 0 and PRODUCT.ProcessStep_ID in (SELECT processstep_id FROM PROCESS_STEP WHERE PS_Type_Name like 'Finished Goods') and PRODUCT.processStep_id=PROCESS_STEP.ProcessStep_ID ORDER BY PRODUCT.Part_Number
 	OPEN bcursor
 		FETCH NEXT FROM bcursor INTO @proc_id, @mft_part_no, @ps_type
 		WHILE @@fetch_status = 0
 		BEGIN
 			INSERT INTO @results VALUES(@proc_id, @mft_part_no, @ps_type )
 			SET @proc_id = null
 			SET @mft_part_no = null
 			SET @ps_type = null
 			
 			FETCH NEXT FROM bcursor INTO @proc_id,@mft_part_no,@ps_type 
 		END
 	CLOSE bcursor
 	DEALLOCATE bcursor
 
 	INSERT INTO @results VALUES(null,'---- KIT -----','---- KIT -----')
 
 	DECLARE kcursor CURSOR FOR
 	SELECT  Process_Step.ProcessStep_ID, Part_Number, P_Type FROM PRODUCT, PROCESS_STEP  WHERE P_Type = 'KIT' and PRODUCT.P_Parent_Product_Id = -1 and PRODUCT.Is_Approved = 0 and  PRODUCT.ProcessStep_ID in ( SELECT Processstep_Id FROM PROCESS_STEP WHERE PS_Type_Name like 'Mark & Pack' or PS_Type_Name like 'Finished Goods' or PS_Type_Name like 'Wafer Store' or PS_Type_Name like 'Die%' or PS_Type_Name like 'Class Store') and PRODUCT.ProcessStep_Id=PROCESS_STEP.ProcessStep_ID ORDER BY PRODUCT.Part_Number
 	OPEN kcursor
 		FETCH NEXT FROM kcursor INTO @proc_id, @mft_part_no, @ps_type
 		WHILE @@fetch_status = 0
 		BEGIN
 			INSERT INTO @results VALUES (@proc_id, @mft_part_no, @ps_type )
 			SET @proc_id = null
 			SET @mft_part_no = null
 			SET @ps_type = null
 			
 			FETCH NEXT FROM kcursor INTO @proc_id,@mft_part_no,@ps_type 
 
 		END
 	CLOSE kcursor
 	DEALLOCATE kcursor
 END

 SELECT * FROM @results   
